﻿namespace WPFDBBrowser.OracleFactory
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Text;
    using Oracle.DataAccess.Client;
    using WPFDBBrowser.Interfaces;
    using WPFDBBrowser.Interfaces.DBTreeView;

    public class OracleDBFactory : IDBFactory
    {
        #region IDBFactory Members

        /// <summary>
        /// Gets the name of the instance.
        /// </summary>
        /// <value>The name.</value>
        public string Name
        {
            get { return this.GetType().FullName; }
        }

        /// <summary>
        /// Gets the friendly name of the instance.
        /// </summary>
        /// <value>The name of the friendly.</value>
        public string FriendlyName
        {
            get { return "Oracle provider"; }
        }

        /// <summary>
        /// Gets the description of the instance.
        /// </summary>
        /// <value>The description.</value>
        public string Description
        {
            get { return "Oracle provider for WPFDBBrowser application."; }
        }

        public string Type
        {
            get { return "WPFDBBrowser.OracleFactory.OracleDBFactory"; }
        }

        /// <summary>
        /// Gets the connection string builder.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns>
        /// Implementation of DbConnectionStringBuilder.
        /// </returns>
        public System.Data.Common.DbConnectionStringBuilder GetConnectionStringBuilder(string connectionString)
        {
            return new OracleConnectionStringBuilder(connectionString);
        }

        /// <summary>
        /// Checks the connection.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns>
        /// Return true, if connection string is correct and connection to DB server was successful; else false.
        /// </returns>
        public bool CheckConnection(string connectionString)
        {
            // Try to open connection
            using (Oracle.DataAccess.Client.OracleConnection connection =
                new Oracle.DataAccess.Client.OracleConnection(connectionString))
                connection.Open();
            return true;
        }
        /// <summary>
        /// Gets the table names.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns>List of table names.</returns>
        public IEnumerable<ITable> GetTables(string connectionString)
        {
            List<ITable> retColl = new List<ITable>();
            using (OracleConnection oraConn = new OracleConnection(connectionString))
            {
                oraConn.Open();
                // Uncomment if you want to get metadata
                //DataTable metadata = oraConn.GetSchema(DbMetaDataCollectionNames.MetaDataCollections);
                ////metadata.WriteXmlSchema(@"D:\OracleSchema.xml");
                //metadata.WriteXml(@"D:\Oracle.xml", XmlWriteMode.WriteSchema);
                DataTable tables = oraConn.GetSchema("Tables", new string[1] { new OracleConnectionStringBuilder(connectionString).UserID });
                foreach (DataRow row in tables.Rows)
                    retColl.Add(new DBTableEntity(this.Type) { Name = (string)row["TABLE_NAME"] });
            }
            return retColl;
        }

        /// <summary>
        /// Gets the view names.
        /// </summary>
        /// <param name="connectionString">The connection string.</param>
        /// <returns>List of view names.</returns>
        public IEnumerable<IView> GetViews(string connectionString)
        {
            IView[] views = null;
            using (OracleConnection oraConn = new OracleConnection(connectionString))
            {
                oraConn.Open();
                // Uncomment if you want to get metadata
                //DataTable metadata = oraConn.GetSchema(DbMetaDataCollectionNames.MetaDataCollections);
                ////metadata.WriteXmlSchema(@"D:\OracleSchema.xml");
                //views.WriteXml(@"D:\Views.xml", XmlWriteMode.WriteSchema);
                DataTable table = oraConn.GetSchema("Views", new string[1] { new OracleConnectionStringBuilder(connectionString).UserID });
                DataRowCollection rows = table.Rows;
                views = new IView[rows.Count];
                for (int i = 0; i < rows.Count; i++)
                    views[i] = new DBViewEntity(this.Type)
                    {
                        Query = (string)rows[i]["TEXT"],
                        Name = (string)rows[i]["VIEW_NAME"]
                    };
            }
            return views;
        }
        public IEnumerable<IColumn> GetColumns(string connectionString, string tableName)
        {
            List<IColumn> columns = new List<IColumn>();
            using (OracleConnection oraConn = new OracleConnection(connectionString))
            {
                oraConn.Open();
                DataTable columnsTable = oraConn.GetSchema("Columns", 
                    new string[] { new OracleConnectionStringBuilder(connectionString).UserID , tableName });
                //columns.WriteXml(@"D:\Columns.xml", XmlWriteMode.WriteSchema);
                foreach (DataRow row in columnsTable.Rows)
                {
                    columns.Add(this.ParseColumnRow(row));
                }
            }
            columns.Sort(delegate(IColumn column1, IColumn column2)
            {
                if (column1 != null && column1.Name != null &&
                    column2 != null)
                    return column1.Name.CompareTo(column2.Name);
                return 0;
            });
            return columns;
        }
        public string GetQuery(IColumnContainer tableOrView, int numberOfRows)
        {
            if (tableOrView.Items == null || tableOrView.Items.Count == 0)
                throw new NullReferenceException("tableOrView.Items");
            string query = string.Format("SELECT {0} FROM {1}", this.GetColumns(tableOrView), tableOrView.Name);
            if (numberOfRows > 0)
                query += string.Format("{0} WHERE {1}", query, this.GetRowsRestriction(numberOfRows));
            return query;
        }
        public string PrepareViewQuery(IView view, int numberOfRows)
        {
            // FIXME: needs to be finished
            // It's not that easy as table, in view we can already have WHERE condition.
            // We need some query parser :-).
            return view.Query;
        }
        public DataTable RunQuery(string connectionString, string query)
        {
            using (OracleConnection oraConn = new OracleConnection(connectionString))
            {
                DataTable table = new DataTable();
                OracleDataAdapter oraAdapter = new OracleDataAdapter(query, oraConn);
                oraAdapter.Fill(table);
                return table;
            }
        }
        #endregion

        public override string ToString()
        {
            return this.FriendlyName;
        }

        #region IDBFactory Members


        #endregion

        #region Private methods
        private string GetRowsRestriction(int numberOfRows)
        {
            return string.Format("ROWNUM <= {0}", numberOfRows);
        }

        private object GetColumns(IColumnContainer tableOrView)
        {
            StringBuilder columns = new StringBuilder();
            foreach (IColumn column in tableOrView.Items)
            {
                if (columns.Length > 0)
                    columns.Append(", ");
                columns.Append(column.Name);
            }
            return columns.ToString();
        }
        private IColumn ParseColumnRow(DataRow row)
        {
            string name = (string)row["COLUMN_NAME"];
            string type = (string)row["DATATYPE"];
            int length = 0;
            int scale = 0;
            bool nullable = row["NULLABLE"].ToString() == "Y";
            switch (type.ToUpper())
            {
                case "NUMBER":
                case "FLOAT":
                    if (row["PRECISION"] == DBNull.Value)
                        length = int.Parse(row["LENGTH"].ToString());
                    else
                    {
                        length = int.Parse(row["PRECISION"].ToString());
                        scale = int.Parse(row["SCALE"].ToString());
                    }
                    break;
                case "VARCHAR2":
                case "NVARCHAR2":
                case "LONG":
                case "BLOB":
                    length = int.Parse(row["LENGTH"].ToString());
                    break;
            }
            return new DBColumn(this.Type) { Name = name, Description = this.GetText(type, length, scale, nullable ? "NULL" : "NOT NULL") };
        }
        private string GetText(string type, int length, int scale, string nullable)
        {
            string text = string.Empty;
            switch (type.ToUpper())
            {
                case "NUMBER":
                case "FLOAT":
                    text += string.Format(" ({0}({1}, {2}), {3})", type, length, scale, nullable);
                    break;
                case "VARCHAR2":
                case "NVARCHAR2":
                case "LONG":
                case "BLOB":
                    text += string.Format(" ({0}({1}), {2})", type, length, nullable);
                    break;
                case "DATE":
                case "TIMESTAMP":
                    text += string.Format(" ({0}, {1})", type, nullable);
                    break;
                default:
                    text += " (unknown)";
                    break;
            }
            return text;
        }
        #endregion
    }
}
